package com.briup.jdbc;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;

/**
 * 使用JDBC完成DML的操作
 * insert delete update
 */
public class DMLTest {
    public static final String driver = "com.mysql.cj.jdbc.Driver";
    public static final String url = "jdbc:mysql:///db01";
    public static final String username = "root";
    public static final String password = "rootroot";
    @Test
    public void insert(){
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url,username,password);
            // 创建语句对象
            statement = connection.createStatement();
            // 准备插入语句
            String sql1 = "insert into t_user values (2,'songqi',21)";
            // 批量插入
            String sql2 = "insert into t_user values (3,'lisi',22)";
            String sql3 = "insert into t_user values (4,'wangwu',23)";
            String sql4 = "insert into t_user values (5,'zhaoliu',24)";
            // 执行DDL的sql语句
            // int i = statement.executeUpdate(sql);
            // 将多个sql语句放入到batch中，批量提交
            statement.addBatch(sql1);
            statement.addBatch(sql2);
            statement.addBatch(sql3);
            statement.addBatch(sql4);
            int[] ints = statement.executeBatch();
            System.out.println("ints = " + Arrays.toString(ints));
            System.out.println("插入成功");

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if(statement!=null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
    @Test
    public void delete(){
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url,username,password);
            // 创建语句对象
            statement = connection.createStatement();
            // 准备插入语句
            String sql = "delete from t_user where id = 1";
            // 执行删除语句
            int i = statement.executeUpdate(sql);
            System.out.println("i = " + i);
            System.out.println("删除成功");

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if(statement!=null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
    @Test
    public void update(){
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url,username,password);
            // 创建语句对象
            statement = connection.createStatement();
            // 准备插入语句
            String sql = "update t_user set name='张三' where id = 1";
            // 执行DDL的sql语句
            int i = statement.executeUpdate(sql);
            System.out.println("i = " + i);
            System.out.println("插入成功");

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if(statement!=null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}
